import sqlite3
import pandas as pd
from contextlib import contextmanager

class SQLiteMemory:
    _instance = None

    # 单例模式
    def __new__(cls):
        if cls._instance is None:
            cls._instance = super(SQLiteMemory, cls).__new__(cls)
            cls._instance.conn = sqlite3.connect(":memory:")
        return cls._instance

    @contextmanager
    def manage_transaction(self):
        cursor = self.conn.cursor()
        try:
            yield cursor
            self.conn.commit()
        except Exception as e:
            print(f"An error occurred: {e}")
            self.conn.rollback()

    # 初始化内存数据库，把DataFrame插入到内存数据库中
    def initialize_memory_database(self, df, table_name, table_schema):
        with self.manage_transaction() as cursor:
            # 在内存数据库中创建表
            cursor.execute(f"CREATE TABLE {table_name} {table_schema}")
            # 将新数据插入到内存数据库的表中
            df.to_sql(table_name, con=self.conn, if_exists='replace', index=False)

    # 查询内存数据库中的数据，如果查询成功，返回DataFrame，否则返回None
    def query(self, sql):
        return pd.read_sql_query(sql, self.conn)

    def delete_from_sqlite(self, table_name, column, value):
        """
        删除SQLite数据库中的记录。

        参数：
        table_name: 数据库中的表名。
        column: 要匹配的列名。
        value: 要匹配的值。
        """
        with self.manage_transaction() as cursor:
            # 构造SQL语句
            sql_delete = f"DELETE FROM {table_name} WHERE {column} = ?"
            # 执行SQL语句
            cursor.execute(sql_delete, (value,))

    # 关闭内存数据库
    def close_memory_database(self):
        self.conn.close()
